﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for DA_Client
/// </summary>
public class DA_Collection
{
    AuditBillingEntities objABEnt;
    public DA_Collection()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public List<PLCollection> DA_GetCollectionTractions(PLCollection pObjCollection)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            List<int> lstArr;
            List<string> lstArrStr;
            var varResult = (from col in objABEnt.TrnCollections
                             join inv in objABEnt.TrnInvoices on col.TrnInvoiceID equals inv.TrnInvoiceID
                             join cn in objABEnt.TrnContracts on inv.TrnContractID equals cn.TrnContractID
                             join cnM in objABEnt.MstContracts on cn.MstContractID equals cnM.MstContractID
                             join cl in objABEnt.MstClients on cn.MstClientID equals cl.MstClientID
                             join payM in objABEnt.MstPaymentModes on cn.MstPaymentModeID equals payM.MstPaymentModeID
                             join payL in objABEnt.MstPaymentLevels on inv.MstPaymentLevelID equals payL.MstPaymentLevelID
                             join com in objABEnt.MstCompanies on inv.MstCompanyID equals com.MstCompanyID
                             where (cn.FinancialYearID ?? 0) == 0
                             select new PLCollection()
                             {
                                 CollectionID = col.TrnCollectionID,
                                 InvoiceID = col.TrnInvoiceID,
                                 ContractID = cn.TrnContractID,
                                 ClientID = cl.MstClientID,
                                 PaymentModeID = payM.MstPaymentModeID,
                                 PaymentLevelID = payL.MstPaymentLevelID,
                                 CompanyID = com.MstCompanyID,

                                 CompanyName = com.Name,
                                 ClientName = cl.ClientName,
                                 ContractName = cnM.Name,
                                 PaymentModeName = payM.PaymentModeName,
                                 ContractNo = cn.ContractNo,
                                 InvoiceNo = inv.InvoiceNo,
                                 CollectionNo = col.CollectionNo,
                                 CollectionDate = col.CollectionDate,
                                 CollectionType = col.CollectionType,
                                 DrawnOnBank = col.DrawnOnBank,
                                 CollectionRefNo = col.CollectionRefNo,
                                 Amount = col.Amount,
                                 TDSAmount = col.TDSAmount,
                                 GrossAmount = col.GrossAmount,
                                 IsCollectionReceived = col.IsCollectionReceived
                             });
            if (!string.IsNullOrEmpty(pObjCollection.SearchClientIDs))
            {
                lstArr = pObjCollection.SearchClientIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.ClientID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjCollection.SearchCompanyIDs))
            {
                lstArr = pObjCollection.SearchCompanyIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.CompanyID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjCollection.SearchContractIDs))
            {
                lstArr = pObjCollection.SearchContractIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.ContractID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjCollection.SearchPaymentModeIDs))
            {
                lstArr = pObjCollection.SearchPaymentModeIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.PaymentModeID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjCollection.SearchContractNo))
            {
                lstArrStr = pObjCollection.SearchContractNo.Split('~').ToList();
                varResult = varResult.Where(s => lstArrStr.Contains(s.ContractNo));
            }
            if (!string.IsNullOrEmpty(pObjCollection.InvoiceNo))
            {
                lstArrStr = pObjCollection.InvoiceNo.Split('~').ToList();
                varResult = varResult.Where(s => lstArrStr.Contains(s.InvoiceNo));
            }


            if (pObjCollection.SearchInvoiceStartDate != null)
                varResult = varResult.Where(s => s.InvoiceDate >= pObjCollection.SearchInvoiceStartDate);
            if (pObjCollection.SearchInvoiceEndDate != null)
                varResult = varResult.Where(s => s.InvoiceDate <= pObjCollection.SearchInvoiceEndDate);
            if (pObjCollection.SearchCollectionStartDate != null)
                varResult = varResult.Where(s => s.CollectionDate >= pObjCollection.SearchCollectionStartDate);
            if (pObjCollection.SearchCollectionEndDate != null)
                varResult = varResult.Where(s => s.CollectionDate <= pObjCollection.SearchCollectionEndDate);

            if (pObjCollection.ContractID.GetValueOrDefault() != 0)
            {
                varResult = varResult.Where(s => s.ContractID == pObjCollection.ContractID);
            }

            return varResult.ToList();
        }
    }

    public PLCollection DA_GetInvoiceDetail(PLCollection pObjCollection)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            var varResult = (from inv in objABEnt.TrnInvoices
                             join cn in objABEnt.TrnContracts on inv.TrnContractID equals cn.TrnContractID
                             join cnM in objABEnt.MstContracts on cn.MstContractID equals cnM.MstContractID
                             join cl in objABEnt.MstClients on cn.MstClientID equals cl.MstClientID
                             join payM in objABEnt.MstPaymentModes on cn.MstPaymentModeID equals payM.MstPaymentModeID
                             join payL in objABEnt.MstPaymentLevels on inv.MstPaymentLevelID equals payL.MstPaymentLevelID
                             join com in objABEnt.MstCompanies on inv.MstCompanyID equals com.MstCompanyID
                             join col in objABEnt.TrnCollections on inv.TrnInvoiceID equals col.TrnInvoiceID into LO_col
                             from col in LO_col.DefaultIfEmpty()
                             where inv.InvoiceNo == pObjCollection.InvoiceNo
                             select new PLCollection()
                             {
                                 InvoiceID = inv.TrnInvoiceID,

                                 CompanyName = com.Name,
                                 ClientName = cl.ClientName,
                                 ContractName = cnM.Name,
                                 PaymentModeName = payM.PaymentModeName,
                                 PaymentLevelName = payL.Name,

                                 Amount = inv.FinalInvoiceAmount,
                                 CollectionAmount = (col == null ? 0 : col.Amount.Value),
                                 IsCollectionReceived = false
                             });
            var varFinalResult = varResult.FirstOrDefault();
            decimal decTotCollAmount = varResult.Sum(s => s.CollectionAmount).Value;
            if (decTotCollAmount >= varFinalResult.Amount)
            {
                varFinalResult.IsCollectionReceived = true;
            }
            else
                varFinalResult.Amount = varFinalResult.Amount - decTotCollAmount;
            return varFinalResult;
        }
    }

    public int DA_InsertUpdateCollections(PLCollection pObjCollection)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            TrnCollection objColl = new TrnCollection();
            if (pObjCollection.CollectionID.GetValueOrDefault() == 0)
            {
                int intlastColelctionNo = TrynParse.parseInt((from i in objABEnt.TrnCollections
                                                              orderby i.TrnCollectionID descending
                                                              select i.CollectionNo).FirstOrDefault());
                objColl.CollectionNo = (intlastColelctionNo + 1).ToString("000000");
                objColl.CreatedBy = SessionInfo.prop_getObj.EmployeeID;
                objColl.CreatedOn = DateTime.Now;
                objColl.IsCollectionReceived = true;
            }
            else
            {
                objColl = (from col in objABEnt.TrnCollections
                           where col.TrnCollectionID == pObjCollection.CollectionID
                           select col).FirstOrDefault();
                objColl.ModifiedBy = SessionInfo.prop_getObj.EmployeeID;
                objColl.ModifiedOn = DateTime.Now;
            }


            objColl.TrnInvoiceID = pObjCollection.InvoiceID.Value;
            objColl.CollectionDate = pObjCollection.CollectionDate;
            objColl.CollectionType = pObjCollection.CollectionType;
            objColl.DrawnOnBank = pObjCollection.DrawnOnBank;
            objColl.CollectionRefNo = pObjCollection.CollectionRefNo;
            objColl.Amount = pObjCollection.Amount;
            objColl.IsTDSDeducted = pObjCollection.IsTDSDeducted;
            objColl.TDSAmount = pObjCollection.TDSAmount;
            objColl.GrossAmount = pObjCollection.Amount - pObjCollection.TDSAmount;
            objColl.Remarks = pObjCollection.Remarks;

            if (pObjCollection.CollectionID.GetValueOrDefault() == 0)
            {
                objABEnt.TrnCollections.Add(objColl);
            }
            objABEnt.SaveChanges();

            var objInvDet = (from col in objABEnt.TrnCollections
                             join inv in objABEnt.TrnInvoices on col.TrnInvoiceID equals inv.TrnInvoiceID
                             where inv.TrnInvoiceID == pObjCollection.InvoiceID.Value
                             select new
                             {
                                 inv.FinalInvoiceAmount,
                                 col.Amount
                             });
            decimal decInvAmt = objInvDet.FirstOrDefault().FinalInvoiceAmount.Value;
            decimal decCollAmt = objInvDet.Sum(s => s.Amount).Value;
            if (decCollAmt >= decInvAmt)
            {
                var varInv = (from inv in objABEnt.TrnInvoices
                              where inv.TrnInvoiceID == pObjCollection.InvoiceID.Value
                              select inv).FirstOrDefault();
                varInv.IsCompleted = true;
                objABEnt.SaveChanges();
            }
            return 1;
        }
    }

    public int DA_DeleteCollections(PLCollection pObjCollection)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            TrnCollection objColl = new TrnCollection();
            objColl = (from col in objABEnt.TrnCollections
                       where col.TrnCollectionID == pObjCollection.CollectionID
                       select col).FirstOrDefault();
            objABEnt.TrnCollections.Remove(objColl);
            objABEnt.SaveChanges();
            return 1;
        }
    }

    public List<PLCollection> DA_GetCollectionReport(PLCollection pObjCollection)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            List<int> lstArr;
            List<string> lstArrStr;
            var varResult = (from col in objABEnt.TrnCollections
                             join inv in objABEnt.TrnInvoices on col.TrnInvoiceID equals inv.TrnInvoiceID
                             join cn in objABEnt.TrnContracts on inv.TrnContractID equals cn.TrnContractID
                             join cnM in objABEnt.MstContracts on cn.MstContractID equals cnM.MstContractID
                             join cl in objABEnt.MstClients on cn.MstClientID equals cl.MstClientID
                             join payM in objABEnt.MstPaymentModes on cn.MstPaymentModeID equals payM.MstPaymentModeID
                             join payL in objABEnt.MstPaymentLevels on inv.MstPaymentLevelID equals payL.MstPaymentLevelID
                             join com in objABEnt.MstCompanies on inv.MstCompanyID equals com.MstCompanyID
                             join fin in objABEnt.FinancialYears on cn.FinancialYearID equals fin.FinancialYearID into LO_Fin
                             from fin in LO_Fin.DefaultIfEmpty()
                             select new PLCollection()
                             {
                                 CollectionID = col.TrnCollectionID,
                                 InvoiceID = col.TrnInvoiceID,
                                 ContractID = cnM.MstContractID,
                                 TranContractID = cn.TrnContractID,
                                 ClientID = cl.MstClientID,
                                 PaymentModeID = payM.MstPaymentModeID,
                                 PaymentLevelID = payL.MstPaymentLevelID,
                                 CompanyID = com.MstCompanyID,
                                 FinancialYearName = (fin == null ? "" : fin.FinYearName),
                                 FinancialYearID = cn.FinancialYearID,

                                 CompanyName = com.Name,
                                 ClientName = cl.ClientName,
                                 ContractName = cnM.Name,
                                 PaymentModeName = payM.PaymentModeName,
                                 ContractNo = cn.ContractNo,
                                 InvoiceNo = inv.InvoiceNo,
                                 CollectionNo = col.CollectionNo,
                                 CollectionDate = col.CollectionDate,
                                 CollectionType = col.CollectionType,
                                 DrawnOnBank = col.DrawnOnBank,
                                 CollectionRefNo = col.CollectionRefNo,
                                 Amount = col.Amount,
                                 TDSAmount = col.TDSAmount,
                                 GrossAmount = col.GrossAmount,

                             });
            if (!string.IsNullOrEmpty(pObjCollection.SearchClientIDs))
            {
                lstArr = pObjCollection.SearchClientIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.ClientID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjCollection.SearchCompanyIDs))
            {
                lstArr = pObjCollection.SearchCompanyIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.CompanyID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjCollection.SearchContractIDs))
            {
                lstArr = pObjCollection.SearchContractIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.ContractID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjCollection.SearchPaymentModeIDs))
            {
                lstArr = pObjCollection.SearchPaymentModeIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.PaymentModeID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjCollection.SearchContractNo))
            {
                lstArrStr = pObjCollection.SearchContractNo.Split('~').ToList();
                varResult = varResult.Where(s => lstArrStr.Contains(s.ContractNo));
            }
            if (!string.IsNullOrEmpty(pObjCollection.InvoiceNo))
            {
                lstArrStr = pObjCollection.InvoiceNo.Split('~').ToList();
                varResult = varResult.Where(s => lstArrStr.Contains(s.InvoiceNo));
            }
            if (!string.IsNullOrEmpty(pObjCollection.FinancialYearIDs))
            {
                lstArr = pObjCollection.FinancialYearIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.FinancialYearID ?? 0));
            }
            else
            {
                varResult = varResult.Where(s => (s.FinancialYearID ?? 0) == 0);
            }
            if (!string.IsNullOrEmpty(pObjCollection.TranContractIDs))
            {
                lstArr = pObjCollection.TranContractIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.TranContractID ?? 0));
            }

            if (pObjCollection.SearchInvoiceStartDate != null)
                varResult = varResult.Where(s => s.InvoiceDate >= pObjCollection.SearchInvoiceStartDate);
            if (pObjCollection.SearchInvoiceEndDate != null)
                varResult = varResult.Where(s => s.InvoiceDate <= pObjCollection.SearchInvoiceEndDate);
            if (pObjCollection.SearchCollectionStartDate != null)
                varResult = varResult.Where(s => s.CollectionDate >= pObjCollection.SearchCollectionStartDate);
            if (pObjCollection.SearchCollectionEndDate != null)
                varResult = varResult.Where(s => s.CollectionDate <= pObjCollection.SearchCollectionEndDate);

            if (pObjCollection.ContractID.GetValueOrDefault() != 0)
            {
                varResult = varResult.Where(s => s.ContractID == pObjCollection.ContractID);
            }

            return varResult.ToList();
        }
    }

    public List<PLCollection> DA_GetCollectionReportFull(PLCollection pObjCollection)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            List<int> lstArr;
            List<string> lstArrStr;
            var varResult = (from col in objABEnt.TrnCollections
                             join inv in objABEnt.TrnInvoices on col.TrnInvoiceID equals inv.TrnInvoiceID
                             join cn in objABEnt.TrnContracts on inv.TrnContractID equals cn.TrnContractID
                             join cnM in objABEnt.MstContracts on cn.MstContractID equals cnM.MstContractID
                             join cl in objABEnt.MstClients on cn.MstClientID equals cl.MstClientID
                             join payM in objABEnt.MstPaymentModes on cn.MstPaymentModeID equals payM.MstPaymentModeID
                             join payL in objABEnt.MstPaymentLevels on inv.MstPaymentLevelID equals payL.MstPaymentLevelID
                             join com in objABEnt.MstCompanies on inv.MstCompanyID equals com.MstCompanyID
                             join fin in objABEnt.FinancialYears on cn.FinancialYearID equals fin.FinancialYearID into LO_Fin
                             from fin in LO_Fin.DefaultIfEmpty()
                             select new PLCollection()
                             {
                                 CollectionID = col.TrnCollectionID,
                                 InvoiceID = col.TrnInvoiceID,
                                 ContractID = cnM.MstContractID,
                                 TranContractID = cn.TrnContractID,
                                 ClientID = cl.MstClientID,
                                 PaymentModeID = payM.MstPaymentModeID,
                                 PaymentLevelID = payL.MstPaymentLevelID,
                                 CompanyID = com.MstCompanyID,
                                 FinancialYearName = (fin == null ? "" : fin.FinYearName),
                                 FinancialYearID = cn.FinancialYearID,

                                 CompanyName = com.Name,
                                 ClientName = cl.ClientName,
                                 ContractName = cnM.Name,
                                 PaymentModeName = payM.PaymentModeName,
                                 PaymentLevelName = payL.Name,
                                 ContractNo = cn.ContractNo,
                                 ContractStartDate = cn.ContractStartDate,
                                 ContractAmount = cn.ContractAmount,
                                 InvoiceDate = inv.InvoiceDate,
                                 ContractPer = inv.PaymentLevelPer,
                                 InvGrossAmount = inv.GrossInvoiceAmount,
                                 ServiceTaxPer = inv.ServiceTaxPer,
                                 ServiceTaxAmount = inv.ServiceTaxAmount,
                                 FinalInvoiceAmount = inv.FinalInvoiceAmount,


                                 InvoiceNo = inv.InvoiceNo,
                                 CollectionNo = col.CollectionNo,
                                 CollectionDate = col.CollectionDate,
                                 CollectionType = col.CollectionType,
                                 DrawnOnBank = col.DrawnOnBank,
                                 CollectionRefNo = col.CollectionRefNo,
                                 Amount = col.Amount,
                                 TDSAmount = col.TDSAmount,
                                 GrossAmount = col.GrossAmount,
                                 Remarks = col.Remarks

                             });
            if (!string.IsNullOrEmpty(pObjCollection.SearchClientIDs))
            {
                lstArr = pObjCollection.SearchClientIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.ClientID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjCollection.SearchCompanyIDs))
            {
                lstArr = pObjCollection.SearchCompanyIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.CompanyID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjCollection.SearchContractIDs))
            {
                lstArr = pObjCollection.SearchContractIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.ContractID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjCollection.SearchPaymentModeIDs))
            {
                lstArr = pObjCollection.SearchPaymentModeIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.PaymentModeID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjCollection.SearchContractNo))
            {
                lstArrStr = pObjCollection.SearchContractNo.Split('~').ToList();
                varResult = varResult.Where(s => lstArrStr.Contains(s.ContractNo));
            }
            if (!string.IsNullOrEmpty(pObjCollection.InvoiceNo))
            {
                lstArrStr = pObjCollection.InvoiceNo.Split('~').ToList();
                varResult = varResult.Where(s => lstArrStr.Contains(s.InvoiceNo));
            }
            if (!string.IsNullOrEmpty(pObjCollection.FinancialYearIDs))
            {
                lstArr = pObjCollection.FinancialYearIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.FinancialYearID ?? 0));
            }
            else
            {
                varResult = varResult.Where(s => (s.FinancialYearID ?? 0) == 0);
            }
            if (!string.IsNullOrEmpty(pObjCollection.TranContractIDs))
            {
                lstArr = pObjCollection.TranContractIDs.Split('~').Select(Int32.Parse).ToList();
                varResult = varResult.Where(s => lstArr.Contains(s.TranContractID ?? 0));
            }

            if (pObjCollection.SearchInvoiceStartDate != null)
                varResult = varResult.Where(s => s.InvoiceDate >= pObjCollection.SearchInvoiceStartDate);
            if (pObjCollection.SearchInvoiceEndDate != null)
                varResult = varResult.Where(s => s.InvoiceDate <= pObjCollection.SearchInvoiceEndDate);
            if (pObjCollection.SearchCollectionStartDate != null)
                varResult = varResult.Where(s => s.CollectionDate >= pObjCollection.SearchCollectionStartDate);
            if (pObjCollection.SearchCollectionEndDate != null)
                varResult = varResult.Where(s => s.CollectionDate <= pObjCollection.SearchCollectionEndDate);

            if (pObjCollection.ContractID.GetValueOrDefault() != 0)
            {
                varResult = varResult.Where(s => s.ContractID == pObjCollection.ContractID);
            }

            return varResult.ToList();
        }
    }
}